<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <title>sql server触发器 | haijd</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  
  <meta name="keywords" content="SQL Server" />
  
  
  
  
  <meta name="description" content="SQL Server触发器">
<meta name="keywords" content="SQL Server">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL Server触发器">
<meta property="og:url" content="http://www.hais2.com/2015/04/16/SQL-Server-Tirgger/index.html">
<meta property="og:site_name" content="haijd">
<meta property="og:description" content="SQL Server触发器">
<meta property="og:locale" content="default">
<meta property="og:updated_time" content="2017-08-02T15:22:33.000Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="SQL Server触发器">
<meta name="twitter:description" content="SQL Server触发器">
  
    <link rel="alternate" href="/atom.xml" title="haijd" type="application/atom+xml">
  
  <link rel="icon" href="/css/images/favicon.ico">
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link href="https://fonts.googleapis.com/css?family=Open+Sans|Montserrat:700" rel="stylesheet" type="text/css">
  <link href="https://fonts.googleapis.com/css?family=Roboto:400,300,300italic,400italic" rel="stylesheet" type="text/css">
  <link href="//cdn.bootcss.com/font-awesome/4.6.3/css/font-awesome.min.css" rel="stylesheet">
  <style type="text/css">
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/9749f0/00000000000000000001008f/27/l?subset_id=2&fvd=n5) format("woff2");font-weight:500;font-style:normal;}
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/90cf9f/000000000000000000010091/27/l?subset_id=2&fvd=n7) format("woff2");font-weight:500;font-style:normal;}
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/8a5494/000000000000000000013365/27/l?subset_id=2&fvd=n4) format("woff2");font-weight:lighter;font-style:normal;}
    @font-face{font-family:futura-pt;src:url(https://use.typekit.net/af/d337d8/000000000000000000010095/27/l?subset_id=2&fvd=i4) format("woff2");font-weight:400;font-style:italic;}</style>
    
  <link rel="stylesheet" id="athemes-headings-fonts-css" href="//fonts.googleapis.com/css?family=Yanone+Kaffeesatz%3A200%2C300%2C400%2C700&amp;ver=4.6.1" type="text/css" media="all">
  <link rel="stylesheet" href="/css/style.css">

  <script src="/js/jquery-3.1.1.min.js"></script>

  <!-- Bootstrap core CSS -->
  <link rel="stylesheet" href="/css/bootstrap.css" >
  <link rel="stylesheet" href="/css/hiero.css" >
  <link rel="stylesheet" href="/css/glyphs.css" >
  
    <link rel="stylesheet" href="/css/vdonate.css" >
  

</head>

<script>
var themeMenus = {};

  themeMenus["/"] = "Home"; 

  themeMenus["/archives"] = "Archives"; 

  themeMenus["/categories"] = "Categories"; 

  themeMenus["/tags"] = "Tags"; 

  themeMenus["/about"] = "About"; 

</script>


  <body data-spy="scroll" data-target="#toc" data-offset="50">


  <header id="allheader" class="site-header" role="banner">
  <div class="clearfix container">
      <div class="site-branding">

          <h1 class="site-title">
            
              <a href="/" title="haijd" rel="home"> haijd </a>
            
          </h1>

          
            <div class="site-description">Stay Hungry,Stay Foolish</div>
          
            
          <nav id="main-navigation" class="main-navigation" role="navigation">
            <a class="nav-open">Menu</a>
            <a class="nav-close">Close</a>
            <div class="clearfix sf-menu">

              <ul id="main-nav" class="nmenu sf-js-enabled">
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/">Home</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/archives">Archives</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/categories">Categories</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/tags">Tags</a> </li>
                    
                      <li class="menu-item menu-item-type-custom menu-item-object-custom menu-item-home menu-item-1663"> <a class="" href="/about">About</a> </li>
                    
              </ul>
            </div>
          </nav>


      </div>
  </div>
</header>




  <div id="container">
    <div id="wrap">
            
      <div id="content" class="outer">
        
          <section id="main" style="float:none;"><article id="post-SQL-Server-Tirgger" style="width: 66%; float:left;" class="article article-type-post" itemscope itemprop="blogPost" >
  <div id="articleInner" class="clearfix post-1016 post type-post status-publish format-standard has-post-thumbnail hentry category-template-2 category-uncategorized tag-codex tag-edge-case tag-featured-image tag-image tag-template">
    
    
      <header class="article-header">
        
  
    <h1 class="thumb" class="article-title" itemprop="name">
      SQL Server触发器
    </h1>
  

      </header>
    
    <div class="article-meta">
      
	Posted on <a href="/2015/04/16/SQL-Server-Tirgger/" class="article-date">
	  <time datetime="2015-04-16T15:33:00.000Z" itemprop="datePublished">April 16, 2015</time>
	</a>

      
	<span id="busuanzi_container_page_pv">
	  本文总阅读量<span id="busuanzi_value_page_pv"></span>次
	</span>

    </div>
    <div class="article-entry" itemprop="articleBody">
      
        <p>　　</p>
<h2 id="什么是触发器"><a href="#什么是触发器" class="headerlink" title="什么是触发器"></a>什么是触发器</h2><p>　　触发器是一种特殊的存储过程，和存储过程不同的是，触发器是由事件被触发时自动进行调用的。<br>　　触发器的执行不是由程序调用，也不是手工启动，而是有事件来触发，比如当对一个表进行操作(insert、delete、update)是就会激活触发器执行。触发器常用于加强数据的完整性约束和业务规则。</p>
<h3 id="分类"><a href="#分类" class="headerlink" title="分类"></a>分类</h3><p>　　SQL Server包括了三种常规类型的触发器：DML触发器、DDL触发器和登录触发器。<a id="more"></a></p>
<h4 id="DML触发器"><a href="#DML触发器" class="headerlink" title="DML触发器"></a>DML触发器</h4><p>　　当数据库中的表中的数据发生变化时(insert、delete、update)，我们如果已经定义了一个触发器(DML触发器)，那么这个触发器就会自动执行。DML触发器的主要作用在于强制执行业务规则，扩展SQL Server约束、默认值等。约束只能约束同一个表中的数据，但是触发器可以执行任意的SQL命令。</p>
<h4 id="DDL触发器"><a href="#DDL触发器" class="headerlink" title="DDL触发器"></a>DDL触发器</h4><p>　　SQL Server2005中新增的触发器，用来审核规范对数据库中表、触发器、视图等结构上的操作，比如在修改表，修改列，新增表，新增列等。它在数据库结构发生变化时执行，我们主要用它来记录数据库的修改过程，以及限制程序员对数据库的修改，比如不允许删除某些指定表等。</p>
<h4 id="登录触发器"><a href="#登录触发器" class="headerlink" title="登录触发器"></a>登录触发器</h4><p>　　登录触发器会相应login事件并自动执行，与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息(例如错误消息和来自print语句的消息)会传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。</p>
<h2 id="编写触发器"><a href="#编写触发器" class="headerlink" title="编写触发器"></a>编写触发器</h2><h3 id="语法"><a href="#语法" class="headerlink" title="语法"></a>语法</h3><p>　　create trigger tr_name<br>　　on 表名 for update|insert|delete<br>　　as<br>　　　　 执行代码…</p>
<h3 id="示例代码"><a href="#示例代码" class="headerlink" title="示例代码"></a>示例代码</h3><p><strong>inster</strong>:<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">trigger</span> tri_insert</span><br><span class="line"><span class="keyword">on</span> student</span><br><span class="line"><span class="keyword">for</span> <span class="keyword">insert</span></span><br><span class="line"><span class="keyword">as</span></span><br><span class="line"><span class="keyword">declare</span> @student_idchar(<span class="number">10</span>)</span><br><span class="line"><span class="keyword">select</span> @student_id=s.student_id <span class="keyword">from</span> students</span><br><span class="line"><span class="keyword">inner</span> <span class="keyword">join</span> insertedion s.student_id=i.student_id</span><br><span class="line"><span class="keyword">if</span> @student_id=<span class="string">'0000000001'</span></span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">raiserror(<span class="string">'不能插入1的学号!'</span>,<span class="number">16</span>,<span class="number">8</span>)</span><br><span class="line">rollbacktran</span><br><span class="line"><span class="keyword">end</span></span><br><span class="line"><span class="keyword">go</span></span><br></pre></td></tr></table></figure></p>
<p><strong>update</strong>:<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">trigger</span> tri_update</span><br><span class="line"><span class="keyword">on</span> student</span><br><span class="line"><span class="keyword">for</span> <span class="keyword">update</span></span><br><span class="line"><span class="keyword">as</span></span><br><span class="line"><span class="keyword">if</span> <span class="keyword">update</span>(student_id)</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">raiserror(<span class="string">'学号不能修改!'</span>,<span class="number">16</span>,<span class="number">8</span>)</span><br><span class="line">rollbacktran</span><br><span class="line"><span class="keyword">end</span></span><br><span class="line"><span class="keyword">go</span></span><br></pre></td></tr></table></figure></p>
<p><strong>delete</strong>:<br><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">trigger</span> tri_delete</span><br><span class="line"><span class="keyword">on</span> student</span><br><span class="line"><span class="keyword">for</span> <span class="keyword">delete</span></span><br><span class="line"><span class="keyword">as</span></span><br><span class="line"><span class="keyword">declare</span> @student_idvarchar(<span class="number">10</span>)</span><br><span class="line"><span class="keyword">select</span> @student_id=student_id <span class="keyword">from</span> deleted</span><br><span class="line"><span class="keyword">if</span> @student_id=<span class="string">'admin'</span></span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line">raiserror(<span class="string">'错误'</span>,<span class="number">16</span>,<span class="number">8</span>)</span><br><span class="line">rollbacktran</span><br><span class="line"><span class="keyword">end</span></span><br></pre></td></tr></table></figure></p>
<p>参考<a href="http://baike.baidu.com/link?url=EgX0DwE_DlHS4WDkzP1_KXARO9scURthngH_fwxltRTZ2f86F1zTXv2Kf9KQ0vjOjkDQbcBHpJzq-jBpx4CELRuIDwsTsVVk--hLbIjQD1W" target="_blank" rel="noopener">百度百科</a></p>

      
    </div>
    <footer class="entry-meta entry-footer">
      
	<span class="ico-folder"></span>
    <a class="article-category-link" href="/categories/SQL/">SQL</a>

      
  <span class="ico-tags"></span>
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/SQL-Server/">SQL Server</a></li></ul>

      
        <div id="donation_div"></div>

<script src="/js/vdonate.js"></script>
<script>
var a = new Donate({
  title: '如果觉得我的文章对您有用，请随意打赏。您的支持将鼓励我继续创作!', // 可选参数，打赏标题
  btnText: 'Donate', // 可选参数，打赏按钮文字
  el: document.getElementById('donation_div'),
  wechatImage: '/css/images/wechatPay.png',
  alipayImage: '/css/images/alipay.jpg'
});
</script>
      

      
        
	<section id="comments" class="comment">
	  <div id="disqus_thread">
	  <noscript>Please enable JavaScript to view the <a href="//disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript>
	  </div>
	</section>

	<script type="text/javascript">
	var disqus_shortname = 'haijdblog';
	(function(){
	  var dsq = document.createElement('script');
	  dsq.type = 'text/javascript';
	  dsq.async = true;
	  dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
	  (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
	}());
	(function(){
	  var dsq = document.createElement('script');
	  dsq.type = 'text/javascript';
	  dsq.async = true;
	  dsq.src = '//' + disqus_shortname + '.disqus.com/count.js';
	  (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
	}());
	</script>


      
    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/2015/04/20/SQL-Server-Join/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          SQL Server表连接(Join)
        
      </div>
    </a>
  
  
    <a href="/2015/04/10/SQL-Server-union/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">SQL Server联合结果集(union)</div>
    </a>
  
</nav>

  
</article>

<!-- Table of Contents -->

  <aside id="sidebar">
    <div id="toc" class="toc-article">
    <strong class="toc-title">Contents</strong>
    
      <ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#什么是触发器"><span class="nav-number">1.</span> <span class="nav-text">什么是触发器</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#分类"><span class="nav-number">1.1.</span> <span class="nav-text">分类</span></a><ol class="nav-child"><li class="nav-item nav-level-4"><a class="nav-link" href="#DML触发器"><span class="nav-number">1.1.1.</span> <span class="nav-text">DML触发器</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#DDL触发器"><span class="nav-number">1.1.2.</span> <span class="nav-text">DDL触发器</span></a></li><li class="nav-item nav-level-4"><a class="nav-link" href="#登录触发器"><span class="nav-number">1.1.3.</span> <span class="nav-text">登录触发器</span></a></li></ol></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#编写触发器"><span class="nav-number">2.</span> <span class="nav-text">编写触发器</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#语法"><span class="nav-number">2.1.</span> <span class="nav-text">语法</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#示例代码"><span class="nav-number">2.2.</span> <span class="nav-text">示例代码</span></a></li></ol></li></ol>
    
    </div>
  </aside>
</section>
        
      </div>
      <footer id="footer" class="site-footer">
  

  <div class="clearfix container">
      <div class="site-info">
	      &copy; 2018 haijd All Rights Reserved.
          
            <span id="busuanzi_container_site_uv">
              本站访客数<span id="busuanzi_value_site_uv"></span>人次  
              本站总访问量<span id="busuanzi_value_site_pv"></span>次
            </span>
          
      </div>
      <div class="site-credit">
        Theme by <a href="https://github.com/iTimeTraveler/hexo-theme-hiero" target="_blank">hiero</a>
      </div>
  </div>
</footer>


<!-- min height -->

<script>
    var contentdiv = document.getElementById("content");

    contentdiv.style.minHeight = document.body.offsetHeight - document.getElementById("allheader").offsetHeight - document.getElementById("footer").offsetHeight + "px";
</script>
    </div>
    <!-- <nav id="mobile-nav">
  
    <a href="/" class="mobile-nav-link">Home</a>
  
    <a href="/archives" class="mobile-nav-link">Archives</a>
  
    <a href="/categories" class="mobile-nav-link">Categories</a>
  
    <a href="/tags" class="mobile-nav-link">Tags</a>
  
    <a href="/about" class="mobile-nav-link">About</a>
  
</nav> -->
    

<!-- mathjax config similar to math.stackexchange -->

<script type="text/x-mathjax-config">
  MathJax.Hub.Config({
    tex2jax: {
      inlineMath: [ ['$','$'], ["\\(","\\)"] ],
      processEscapes: true
    }
  });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
      tex2jax: {
        skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
      }
    });
</script>

<script type="text/x-mathjax-config">
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for(i=0; i < all.length; i += 1) {
            all[i].SourceElement().parentNode.className += ' has-jax';
        }
    });
</script>

<script type="text/javascript" src="https://cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML">
</script>


  <link rel="stylesheet" href="/fancybox/jquery.fancybox.css">
  <script src="/fancybox/jquery.fancybox.pack.js"></script>


<script src="/js/scripts.js"></script>
<script src="/js/bootstrap.js"></script>
<script src="/js/main.js"></script>








	<script async src="//dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js">
	</script>






  </div>

  <a id="rocket" href="#top" class=""></a>
  <script type="text/javascript" src="/js/totop.js" async=""></script>
</body>
</html>
